package cn.glutnn.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.glutnn.entity.Notice;

public class JdbcUtils {

	private static String dbname = "day0104";
	private static String username = "root";
	private static String password = "root";

	// 单例模式获取
	private static Connection conn = getConnection();

	/**
	 * 获取连接实例，单例模式
	 */
	private static Connection getConnection() {
		try {

			// 加载mysql驱动程序

			Class.forName("com.mysql.jdbc.Driver");
			// 连接localhost上的mysql,用户名为root,密码为root

			conn = DriverManager.getConnection("jdbc:mysql://localhost/"
					+ dbname + "?useUnicode=true&characterEncoding=utf-8",
					username, password);
			if (!conn.isClosed()) {
				// System.out.println("数据库连接成功！"); // 验证是否连接成功

			} else {
				System.out.println("数据库连接失败！");
			}

			return conn;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

	/**
	 * 
	 * 查询list集合
	 */
	public static <T> List<T> query(String sql, Class<T> type) {
		QueryRunner qr = new QueryRunner();
		try {
			return qr.query(JdbcUtils.conn, sql, new BeanListHandler<T>(type));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return new ArrayList<>();
	}

	/**
	 * 
	 * 查询单个结果
	 */
	public static <T> T queryOne(String sql, Class<T> type) {
		List<T> list = query(sql, type);
		if (list.size() > 1) {
			System.out.println("多于一个结果");
			return null;
		}
		if (list.size() == 1) {
			return list.get(0);
		}

		return null;
	}

	/**
	 * 通用保存方法（采用反射实现）
	 */
	public static <T> boolean save(T entity) {
		Integer id = null;
		try {

			String tablename = entity.getClass().getSimpleName().toLowerCase();
			Field[] fields = entity.getClass().getDeclaredFields();

			List<String> names = new ArrayList<>();
			List<Object> params = new ArrayList<>();
			
			Field idField = null;

			for (int i = 0, ii = fields.length; i < ii; i++) {
				Field f = fields[i];
				f.setAccessible(true);
				String name = f.getName();
				if (name.equals("id")) {
					idField = f;
					continue;
				}
				names.add(name);
				params.add(f.get(entity));
			}

			String fs = "";
			String fs2 = "";
			for (int i = 0, ii = names.size(); i < ii; i++) {
				if (i != 0) {
					fs += ",";
					fs2 += ",";
				}
				fs += names.get(i);
				fs2 += "?";
			}

			String sql = "insert into t_" + tablename + "(" + fs + ") values("
					+ fs2 + ")";

			QueryRunner qr = new QueryRunner();
			Long _id = qr.insert(conn, sql, new ScalarHandler<Long>(),
					params.toArray());
			id = Integer.valueOf(_id.toString());
			idField.set(entity, id);
			
		} catch (Exception e) {
			System.out.println(e);
		}

		return id != null;
	}

	public static <T> boolean update(T entity) {

		int t = 0;
		try {
			Integer id = null;
			
			String tablename = entity.getClass().getSimpleName().toLowerCase();
			Field[] fields = entity.getClass().getDeclaredFields();

			List<String> names = new ArrayList<>();
			List<Object> params = new ArrayList<>();

			for (int i = 0, ii = fields.length; i < ii; i++) {
				Field f = fields[i];
				f.setAccessible(true);
				String name = f.getName();
				if (name.equals("id")) {
					id = (Integer) f.get(entity);
					continue;
				}
				names.add(name);
				params.add(f.get(entity));
			}
			
			if (id == null) {
				System.out.println("错误：没有id值");
				return false;
			}

			String fs = "";
			for (int i = 0, ii = names.size(); i < ii; i++) {
				if (i != 0) {
					fs += ",";
				}
				
				fs += names.get(i) + "=?";
			}

			String sql = "update t_" + tablename + " set " + fs + " where id = " + id;

			QueryRunner qr = new QueryRunner();
			t = qr.update(conn, sql, params.toArray());
		} catch (Exception e) {
			System.out.println(e);
		}

		return t != 0;
	}

	/**
	 * 通用删除方法
	 * 
	 * @param id
	 *            数据库表关键字
	 */
	private static boolean delete(Integer id, String whatTable) {

		boolean isOK = true;
		QueryRunner qr = new QueryRunner();
		// (完成) 实现删除数据库逻辑

		String sql = "delete from t_" + whatTable + " where id = ?";
		try {
			qr.update(conn, sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
			isOK = false;
		}

		return isOK;
	}

	/**
	 * 通用删除方法
	 */
	public static <T> boolean delete(Integer id, Class<T> clazz) {
		String entityName = clazz.getSimpleName().toLowerCase();
		return delete(id, entityName);
	}

}
